﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace carApp
{
    public partial class Ticket : Form
    {
        public Ticket()
        {
            InitializeComponent();
        }
        int dgRow = 0;
        int dgCol = 0;
        private void button1_Click(object sender, EventArgs e)
        {
            //panel_addGoods.Visible = true;
            GoodsSelect goods = new GoodsSelect();
            goods.Owner = this;
            goods.ShowDialog();
        }

        private void Ticket_Load(object sender, EventArgs e)
        {

        }
        public void addGoodsToDataView(string goodsId)
        {
            if (goodsId.Length > 0)
            {
                DataTable dt = db.GetTable("goods", "id in(" + goodsId + ")");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var id = dt.Rows[i]["id"].ToString();
                    var sn = dt.Rows[i]["sn"].ToString();
                    var name = dt.Rows[i]["name"].ToString();
                    var spec = dt.Rows[i]["spec"].ToString();
                    var unit = dt.Rows[i]["unit"].ToString();
                    var color = dt.Rows[i]["color"].ToString();
                    var price = dt.Rows[i]["price"].ToString();
                    var totalPrice = price;
                    dataGridView1.Rows.Add(new object[] { id, sn, name, spec, unit, color, "1", price, totalPrice });
                }
            }
        }
        private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                dgRow = dataGridView1.SelectedCells[0].RowIndex;
                dgCol = dataGridView1.SelectedCells[0].ColumnIndex;
            }
            catch
            { }
        }

        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 1)
            {
                try
                {
                    var sn = dataGridView1.Rows[dgRow].Cells[1].Value.ToString();
                    Console.WriteLine(sn);
                    if (sn.Length > 0)
                    {
                        DataTable dt = db.GetTable("goods", "sn='" + sn + "'");
                        if (dt.Rows.Count > 0)
                        {
                            dataGridView1.Rows[dgRow].Cells[2].Value = dt.Rows[0]["name"].ToString();
                            dataGridView1.Rows[dgRow].Cells[3].Value = dt.Rows[0]["spec"].ToString();
                            dataGridView1.Rows[dgRow].Cells[4].Value = dt.Rows[0]["unit"].ToString();
                            dataGridView1.Rows[dgRow].Cells[5].Value = dt.Rows[0]["color"].ToString();
                            dataGridView1.Rows[dgRow].Cells[6].Value = "1";
                            dataGridView1.Rows[dgRow].Cells[7].Value = dt.Rows[0]["price"].ToString();
                            dataGridView1.Rows[dgRow].Cells[8].Value = dt.Rows[0]["price"].ToString();
                        }
                    }
                }
                catch(Exception ex)
                { }
            }
            else if(e.ColumnIndex==6)
            {
                decimal newPrice= decimal.Parse(dataGridView1.Rows[dgRow].Cells[6].Value.ToString())* decimal.Parse(dataGridView1.Rows[dgRow].Cells[7].Value.ToString());
                dataGridView1.Rows[dgRow].Cells[8].Value = Math.Round(newPrice, 2);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //if (dataGridView1.Rows.Count > 1)
            //{
            DataTable dt = new DataTable();
            if(dataGridView1.Rows.Count>1)
                dt = GetDgvToTable(dataGridView1);
            //}
            //else
            //{
            //    MessageBox.Show("请添加产品信息");
            //}
            Model.Ticket ticket = new Model.Ticket();
            ticket.acceper = txtAcceper.Text;
            ticket.accep_type = cmbAccep_type.Text;
            ticket.amount = decimal.Parse(txtAmount.Text);
            ticket.danwei = txtDanwei.Text;
            ticket.mark = txtMark.Text;
            ticket.maker = txtMaker.Text;
            ticket.payer = txtPayer.Text;
            int payStatus = 1;
            if (rdb_pay0.Checked)
                payStatus = 0;
            ticket.pay_status = payStatus;
            ticket.pay_type = cmbPay_type.Text;
            ticket.print_num = 0;
            ticket.times = dtp_times.Value.ToString("yyyy-MM-dd HH:mm:ss");
            ticket.items = db.DataTableToJson(dt);
            if(db.add(ticket)>0)
            {
                MessageBox.Show("保存成功");
            }
            else
            {
                MessageBox.Show("保存失败");
            }
        }
        public DataTable GetDgvToTable(DataGridView dgv)
        {
            DataTable dt = new DataTable();            
            for (int count = 0; count < dgv.Columns.Count; count++)
            {
                DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
                dt.Columns.Add(dc);
            }
            for (int count = 0; count < dgv.Rows.Count; count++)
            {
                DataRow dr = dt.NewRow();
                for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
                {
                    dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.RowIndex > -1)
            {
                int index = dataGridView1.CurrentRow.Index;
                if (index > -1)
                {
                    //int id = int.Parse(dataGridView1.Rows[index].Cells[0].Value.ToString());
                    dataGridView1.Rows.RemoveAt(index);
                }
            }
        }
        /// <summary>
        /// 选择相应的Excel文件
        /// </summary>
        /// <param name="senderQ"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
            ImportExcel();
        }
        public void OutPortExcel()
        {
            try
            {
                //没有数据的话就不往下执行  
                if (dataGridView1.Rows.Count == 0)
                    return;
                //实例化一个Excel.Application对象  
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

                //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写  
                excel.Visible = true;

                //新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错  
                excel.Application.Workbooks.Add(true);
                //生成Excel中列头名称  
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                {
                    if (this.dataGridView1.Columns[i].Visible == true)
                    {
                        excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
                    }

                }
                //把DataGridView当前页的数据保存在Excel中  
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    System.Windows.Forms.Application.DoEvents();
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        if (this.dataGridView1.Columns[j].Visible == true)
                        {
                            if (dataGridView1[j, i].ValueType == typeof(string))
                            {
                                excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
                            }
                            else
                            {
                                excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
                            }
                        }

                    }
                }

                //设置禁止弹出保存和覆盖的询问提示框  
                excel.DisplayAlerts = false;
                excel.AlertBeforeOverwriting = false;

                //保存工作簿  
                excel.Application.Workbooks.Add(true).Save();
                //保存excel文件  
                excel.Save("D:" + "\\KKHMD.xls");

                //确保Excel进程关闭  
                excel.Quit();
                excel = null;
                GC.Collect();//如果不使用这条语句会导致excel进程无法正常退出，使用后正常退出
                MessageBox.Show(this, "文件已经成功导出！", "信息提示");

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示");
            }
        }
        public void ImportExcel() { 
            try
            {
                OpenFileDialog odXls = new OpenFileDialog();
                odXls.InitialDirectory = "C://";
                odXls.Filter = "Excel files (*.xls)|*.xls";
                odXls.FilterIndex = 2;
                odXls.RestoreDirectory = true;
                if (odXls.ShowDialog() == DialogResult.OK)
                {
                    var fileName = odXls.FileName;
                    System.Data.OleDb.OleDbConnection oledbConn = null;
                    string sConnString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + odXls.FileName + ";Extended Properties=Excel 5.0;Persist Security Info=False";
                    oledbConn = new System.Data.OleDb.OleDbConnection(sConnString);
                    oledbConn.Open();
                    string[] names = GetExcelSheetNames(oledbConn);
                    if (names.Length > 0)
                    {
                        foreach (string f_name in names)
                        {
                            System.Data.OleDb.OleDbCommand cmd = oledbConn.CreateCommand();
                            cmd.CommandText = string.Format(" select * from [{0}]", f_name);//[sheetName]要如此格式
                            System.Data.OleDb.OleDbDataReader odr = cmd.ExecuteReader();
                            while (odr.Read())
                            {
                                var id = "0";
                                var sn = odr[0].ToString();
                                var name = odr[1].ToString();
                                var spec = odr[2].ToString();
                                var unit = odr[3].ToString();
                                var color = odr[4].ToString();
                                var num = odr[5].ToString();
                                var price = odr[6].ToString();
                                var totalPrice = odr[7].ToString();
                                dataGridView1.Rows.Add(new object[] { id, sn, name, spec, unit, color, num, price, totalPrice });
                            }
                            odr.Close();
                        }
                    }                   

                    if (oledbConn != null)
                        oledbConn.Close();
                }
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
            }
        }
        /// <summary>
        /// 查询表名
        /// </summary>
        /// <param name="con"></param>
        /// <returns></returns>
        public static string[] GetExcelSheetNames(System.Data.OleDb.OleDbConnection con)
        {
            try
            {
                System.Data.DataTable dt = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new[] { null, null, null, "Table" });//检索Excel的架构信息
                var sheet = new string[dt.Rows.Count];
                for (int i = 0, j = dt.Rows.Count; i < j; i++)
                {
                    //获取的SheetName是带了$的
                    sheet[i] = dt.Rows[i]["TABLE_NAME"].ToString();
                }
                return sheet;
            }
            catch
            {
                return null;
            }
        }
    }
}
